package com.cpit.platform.commontable.dao.impl;

import com.cpit.platform.common.dao.Criterion;
import com.cpit.platform.common.dao.SimpleExpression;
import com.cpit.platform.commontable.dao.CommonTableRepository;
import org.hibernate.transform.Transformers;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
import org.springframework.stereotype.Repository;
import org.springframework.util.StringUtils;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import javax.persistence.criteria.CriteriaBuilder;
import java.io.Serializable;
import java.math.BigInteger;
import java.util.*;

@Repository
public class CommonTableRepositoryImpl<ID extends Serializable> implements CommonTableRepository<ID> {
    private static final String loggerName = CommonTableRepositoryImpl.class.getName();
    @PersistenceContext
    private EntityManager entityManager;

    public Map save(String tableName,Map entity){
        StringBuilder sbColumnNames = new StringBuilder();
        StringBuilder sbColumnValues = new StringBuilder();
        int count = 0;
        for (Object columnName : entity.keySet()) {
            if(count == 0){
                sbColumnNames.append(" (").append(columnName);
                sbColumnValues.append(" (").append(entity.get(columnName));
            }else if(count == entity.keySet().size() - 1){
                sbColumnNames.append(",").append(columnName).append(") ");
                sbColumnValues.append(",").append(entity.get(columnName)).append(") ");
            }else{
                sbColumnNames.append(",").append(columnName);
                sbColumnValues.append(",").append(entity.get(columnName));
            }
            count++;
        }
        StringBuilder sb = new StringBuilder();
        sb.append("insert into ").append(tableName).append(sbColumnNames).append(" values ").append(sbColumnValues);
        Query query=entityManager.createNativeQuery(sb.toString());
        Map map = (Map)query.getSingleResult();
        return map;
    }


    public Integer modify(String tableName,Long id,Map entity){
        StringBuilder sbColumns = new StringBuilder();
        int count = 0;
        for (Object columnName : entity.keySet()) {
            if(count == 0){
                sbColumns.append(columnName).append("=").append(entity.get(columnName));
            }else{
                sbColumns.append(",").append(columnName).append("=").append(entity.get(columnName));
            }
            count++;
        }
        StringBuilder sb = new StringBuilder();
        sb.append("update ").append(tableName).append(" set ").append(sbColumns).append(" WHERE id=").append(id);
        Query query=entityManager.createNativeQuery(sb.toString());
        Integer result = query.executeUpdate();
        return result;
    }

    public List findAll(String tableName){
        StringBuilder sb = new StringBuilder();
        sb.append("select * from ").append(tableName);
        Query query=entityManager.createNativeQuery(sb.toString());
        query.unwrap(org.hibernate.SQLQuery.class)
                .setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        List<Map> list = query.getResultList();
        return list;
    }
    public int delete(String tableName,Long id){
        StringBuilder sb = new StringBuilder();
        sb.append("delete from ").append(tableName).append(" WHERE id=").append(id);
        Query query=entityManager.createNativeQuery(sb.toString());
        Integer result = query.executeUpdate();
        return result;
    }
    public int delete(String tableName, Map queryCondMap ,List<SimpleExpression> queryCondSimple){
        StringBuilder sb = new StringBuilder();
        sb.append("delete from ").append(tableName).append(" WHERE true ").append(processCriteria(tableName,queryCondMap,queryCondSimple));
        Query query=entityManager.createNativeQuery(sb.toString());
        Integer result = query.executeUpdate();
        return result;
    }

    @Override
    public List findByColumnEqual(String tableName, String columnName, Object columnValue ) {
        String sql="select * from "+tableName+" u WHERE u."+columnName+"="+columnName;
        Query query=entityManager.createNativeQuery(sql);
        query.unwrap(org.hibernate.SQLQuery.class)
                .setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        List<Map<String, Object>> list = query.getResultList();
        return list;
    }
    public List findByCondition(String tableName, SimpleExpression simpleExpression) {
        List list = new ArrayList(1);
        list.add(simpleExpression);
        return  this.findByCondition(tableName,null,list);
    }
    public  Page<Map<String, Object>>  findByCondition(String tableName, SimpleExpression simpleExpression, Pageable pageable) {
        List list = new ArrayList(1);
        list.add(simpleExpression);
        return  this.findByCondition(tableName,(Map)null,list,pageable);
    }

    public List findByCondition(String tableName,  Map queryCondMap) {
        return  this.findByCondition(tableName,queryCondMap,(List<SimpleExpression>)null);
    }
    public  Page<Map<String, Object>>  findByCondition(String tableName,  Map queryCondMap, Pageable pageable) {
        return  this.findByCondition(tableName,queryCondMap,(List<SimpleExpression>)null,pageable);
    }

    public Page<Map<String, Object>> findByCondition(String tableName, Map queryCondMap ,List<SimpleExpression> queryCondSimple, Pageable pageable) {
        StringBuilder countSqlFixCondition = getJpqlCountSqlCondition(tableName,queryCondMap,queryCondSimple);
        Query queryCount = entityManager.createNativeQuery(countSqlFixCondition.toString());
        Long recordCount = ((BigInteger)queryCount.getSingleResult()).longValue();
        StringBuilder orderByJpqlSqlCondition = getOrderByJpqlSqlCondition(tableName,queryCondMap,queryCondSimple,pageable);
        String queryJpqlSqlCondition = getQueryJpqlSqlCondition(tableName,queryCondMap,queryCondSimple).append(orderByJpqlSqlCondition).toString();
        Query query = entityManager.createNativeQuery(queryJpqlSqlCondition);
        query.unwrap(org.hibernate.SQLQuery.class)
                .setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        query.setMaxResults(pageable.getPageSize());   //计数从0开始
        query.setFirstResult(pageable.getPageNumber() * pageable.getPageSize());
        return new PageImpl<>(query.getResultList(), pageable, recordCount);
    }

    /**
     * 根据可变条件查询设备列表
     * @return 符合查询条件的項目
     */
    public List findByCondition(String tableName, Map queryCondMap ,List<SimpleExpression> queryCondSimple) {
        String queryJpqlSqlCondition = getQueryJpqlSqlCondition(tableName,queryCondMap,queryCondSimple).toString();
        Query query = entityManager.createNativeQuery(queryJpqlSqlCondition);
        query.unwrap(org.hibernate.SQLQuery.class)
                .setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        return query.getResultList();
    }

    private StringBuilder getJpqlCountSqlCondition(String tableName, Map queryCondMap ,List<SimpleExpression> queryCondSimple){
        StringBuilder sb =  new StringBuilder();
        sb.append("SELECT count(*)")
                .append(" from ").append(tableName).append(" t ")
                .append(" where true ").append(processCriteria(tableName,queryCondMap , queryCondSimple));
        return  sb;
    }
    private StringBuilder getQueryJpqlSqlCondition(String tableName, Map queryCondMap ,List<SimpleExpression> queryCondSimple){
        StringBuilder sb = new StringBuilder();
        sb.append("SELECT *")
                .append(" from ").append(tableName).append(" t ")
                .append(" where true ").append(processCriteria(tableName,queryCondMap , queryCondSimple));
        return  sb;
    }

    private StringBuilder getOrderByJpqlSqlCondition(String tableName, Map queryCondMap ,List<SimpleExpression> queryCondSimple,Pageable pageable){
        final String STRING_ORDER_BY = " order by ";
        StringBuilder stringBuilder = new StringBuilder("");
        Sort sort = pageable.getSort();
        if (sort != null) {
            stringBuilder.append(STRING_ORDER_BY);
            Iterator<Sort.Order> iterator = sort.iterator();
            while (iterator.hasNext()) {
                Sort.Order order = iterator.next();
                if (order == null)
                    break;
                stringBuilder.append("t."+order.getProperty());
                stringBuilder.append(" ").append(order.getDirection().name()).append(",");
            }
        }
        if (stringBuilder.lastIndexOf(",") > 0){
            stringBuilder.setLength(stringBuilder.length() - 1);
        }
        if (stringBuilder.length() == STRING_ORDER_BY.length()){
            stringBuilder.setLength(0);
        }
        return stringBuilder;
    }

    public Class getColumnType( List<Map> columnMetaList,String fieldName){
        Class columnClass = null;
        if(columnMetaList != null && !StringUtils.isEmpty(fieldName)){
            for(Map columnMeta:columnMetaList){
                if(fieldName.equalsIgnoreCase((String)columnMeta.get("Field"))){
                    String type = (String)columnMeta.get("Type");
                    if(type.indexOf("bigint") != -1){
                        columnClass = Long.class;
                    }else if(type.indexOf("int") != -1){
                        columnClass = Integer.class;
                    }else if(type.indexOf("datetime") != -1 || type.indexOf("date") != -1 || type.indexOf("time") != -1){
                        columnClass = Date.class;
                    }else if(type.indexOf("double") != -1){
                        columnClass = Double.class;
                    }else if(type.indexOf("float") != -1){
                        columnClass = Float.class;
                    }else if(type.indexOf("text") != -1 || type.indexOf("char") != -1){
                        columnClass = String.class;
                    }
                }
            }
        }
        return columnClass;
    }
    public List<Map> getColumnMetaList(String tableName){
        Query queryTableMetaData = entityManager.createNativeQuery("show COLUMNS from "+tableName+";");
        queryTableMetaData.unwrap(org.hibernate.SQLQuery.class)
                .setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        List<Map> columnMetaList =queryTableMetaData.getResultList();
        return columnMetaList;
    }
    private StringBuilder processCriteria(String tableName,Map queryCondMap , List<SimpleExpression> queryCondSimple) {
        StringBuilder  criteria= new StringBuilder();
        List<Map> columnMetaList = getColumnMetaList(tableName);
        if(queryCondMap != null && queryCondMap.size() > 0){
            Set<String> fieldNames =  queryCondMap.keySet();
            for(String fieldName:fieldNames){
                Object valObject = queryCondMap.get(fieldName);
                if (valObject == null) {
                    continue;
                }

                Class columnClass = getColumnType( columnMetaList,fieldName);

                    if(columnClass != null && ((Number.class.isAssignableFrom(columnClass) || Date.class.isAssignableFrom(columnClass)) && (valObject instanceof String))) {
                        String fVal = ((String) valObject).trim();
                        if (fVal.contains(Criterion.SEPARATOR_SEMICOLON)) {

                            String[] minMaxConds = fVal.split(Criterion.SEPARATOR_SEMICOLON);
                            if (minMaxConds.length == 1) {
                                Object oValue = convertValue2Object(columnClass, minMaxConds[0]);
                                if (!fVal.contains(Criterion.SEPARATOR_SEMICOLON)) {
//                                criteria.add(Restrictions.eq(fieldName, oValue));
                                    criteria.append(" and ").append(fieldName).append(" = ").append(oValue);
                                } else if (fVal.startsWith(Criterion.SEPARATOR_SEMICOLON)) {
                                    if (oValue instanceof Date) {
//                                    criteria.add(Restrictions.lt(fieldName, DateUtil.getNextDay((Date)oValue)));
                                        criteria.append(" and ").append(fieldName).append(" < ").append(oValue);
                                    } else {
//                                    criteria.add(Restrictions.lte(fieldName, oValue));
                                        criteria.append(" and ").append(fieldName).append(" <= ").append(oValue);
                                    }
                                } else if (fVal.endsWith(Criterion.SEPARATOR_SEMICOLON)) {
//                                criteria.add(Restrictions.gte(fieldName, oValue));
                                    criteria.append(" and ").append(fieldName).append(" >= ").append(oValue);
                                }
                            } else if (minMaxConds.length > 1) {
                                minMaxConds[0] = (StringUtils.isEmpty(minMaxConds[0]) ? null : minMaxConds[0].trim());
                                minMaxConds[1] = (StringUtils.isEmpty(minMaxConds[1]) ? null : minMaxConds[1].trim());
                                Object minValue = convertValue2Object(columnClass, minMaxConds[0]);
                                Object maxValue = convertValue2Object(columnClass, minMaxConds[1]);
                                if (minValue != null) {
//                                criteria.add(Restrictions.gte(fieldName, minValue));
                                    criteria.append(" and ").append(fieldName).append(" >= ").append(minValue);
                                }
                                if (maxValue != null) {
                                    if (maxValue instanceof Date) {
//                                    criteria.add(Restrictions.lt(fieldName, DateUtil.getNextDay((Date)maxValue)));
                                        criteria.append(" and ").append(fieldName).append(" < ").append(maxValue);
                                    } else {
//                                    criteria.add(Restrictions.lte(fieldName, maxValue));
                                        criteria.append(" and ").append(fieldName).append(" <= ").append(maxValue);
                                    }
                                }
                            }
                        } else if (fVal.startsWith("[") && fVal.endsWith("]")) {
                            String[] arr = fVal.substring(1, fVal.length()-2).split(",");
                            StringBuilder sbIn = processCriteriaInCondition(arr);
                            criteria.append(" and ").append(" in ").append(sbIn);
                        }
                    } else if(String.class.isAssignableFrom(columnClass) && (valObject instanceof String)){
                        String strVal = ((String) valObject).trim();
                        if (!StringUtils.isEmpty(strVal) && !strVal.equals("%") && !strVal.equals("%%")) {
                            if (strVal.startsWith("%")) {
                                if (strVal.endsWith("%") && strVal.length()>2) {
//                                    criteria.add(Restrictions.like(fieldName, strVal.substring(1,strVal.length()-1), Criterion.MatchMode.ANYWHERE));
                                    criteria.append(" and ").append(fieldName).append(" like '%").append(strVal.substring(1,strVal.length()-1)).append("%'");
                                } else {
//                                    criteria.add(Restrictions.like(fieldName, strVal.substring(1), Criterion.MatchMode.START));
                                    criteria.append(" and ").append(fieldName).append(" like '").append(strVal.substring(1)).append("%'");
                                }
                            } else if (strVal.endsWith("%")) {
//                                criteria.add(Restrictions.like(fieldName, strVal.substring(0, strVal.length()-1), Criterion.MatchMode.END));
                                criteria.append(" and ").append(fieldName).append(" like '%").append(strVal.substring(0, strVal.length()-1)).append("'");
                            }else if (strVal.startsWith("[") && strVal.endsWith("]")) {
                                String[] arr = strVal.substring(1, strVal.length()-2).split(",");
                                StringBuilder sbIn = processCriteriaInCondition(arr);
                                criteria.append(" and ").append(" in ").append(sbIn);
                            } else {
//                                criteria.add(Restrictions.eq(fieldName, strVal));
                                criteria.append(" and ").append(fieldName).append(" = ").append(strVal);
                            }
                        }
                    } else {
//                        criteria.add(Restrictions.eq(fieldName,valObject));
                        criteria.append(" and ").append(fieldName).append(" = ").append(valObject);
                    }

            }
        }
        //二、处理简单条件列表
        if (queryCondSimple != null && queryCondSimple.size() > 0){
            for(SimpleExpression se:queryCondSimple){
//                Field field = getField(se.getFieldName());
                String fieldName = se.getFieldName();
                Class columnClass = getColumnType( columnMetaList,fieldName);
                if(columnClass != null){
                    se.setValue(convertValue2Object(columnClass,se.getValue()));
                    se.setMinValue(convertValue2Object(columnClass,se.getMinValue()));
                    se.setMaxValue(convertValue2Object(columnClass,se.getMaxValue()));
                    List valueList = se.getValueList();
                    if (valueList != null &&  valueList.size() >0){
                        List newValueList = new ArrayList( valueList.size());
                        Object tmpValue;
                        for (int i = 0; i < valueList.size(); i++){
                            tmpValue = convertValue2Object(columnClass,valueList.get(i));
                            if (tmpValue != null){
                                newValueList.add(tmpValue);
                            }
                        }
                        se.setValueList(newValueList);
                    }
                    Object value = se.getValue();
                    Object minValue = se.getMinValue();
                    Object maxValue = se.getMaxValue();
                    Criterion.MatchMode matchMode = se.getMatchMode();
                    switch (se.getOperator()) {
                        case EQ:
//                            return builder.equal(expression, value);
                            criteria.append(" and ").append(fieldName).append(" = ").append(value);
                            break;
                        case NE:
//                            return builder.notEqual(expression, value);
                            criteria.append(" and ").append(fieldName).append(" != ").append(value);
                            break;
                        case LIKE:
                            switch(matchMode){
                                case START :
//                                    return builder.like((Expression<String>) expression, value + "%");
                                    criteria.append(" and ").append(fieldName).append(" like '").append(value).append("%'");
                                    break;
                                case END :
//                                    return builder.like((Expression<String>) expression, "%" + value);
                                    criteria.append(" and ").append(fieldName).append(" like '%").append(value).append("'");
                                    break;
                                case ANYWHERE :
//                                    return builder.like((Expression<String>) expression, "%" + value + "%");
                                    criteria.append(" and ").append(fieldName).append(" like '%").append(value).append("%'");
                                    break;
                                default :
//                                    return builder.like((Expression<String>) expression, "%" + value + "%");
                                    criteria.append(" and ").append(fieldName).append(" like '%").append(value).append("%'");
                                    break;
                            }
                        case LT:
//                            return builder.lessThan(expression, (Comparable) value);
                            criteria.append(" and ").append(fieldName).append(" < ").append(value);
                            break;
                        case GT:
//                            return builder.greaterThan(expression, (Comparable) value);
                            criteria.append(" and ").append(fieldName).append(" > ").append(value);
                            break;
                        case LTE:
//                            return builder.lessThanOrEqualTo(expression, (Comparable) value);
                            criteria.append(" and ").append(fieldName).append(" <= ").append(value);
                            break;
                        case GTE:
//                            return builder.greaterThanOrEqualTo(expression, (Comparable) value);
                            criteria.append(" and ").append(fieldName).append(" >= ").append(value);
                            break;
                        case ISNOTNULL:
//                            return builder.isNotNull(expression);
                            criteria.append(" and ").append(fieldName).append(" >= ").append(value);
                            break;
                        case ISNULL:
//                            return builder.isNull(expression);
                            criteria.append(" and ").append(fieldName).append(" != null ");
                            break;
                        case ISEMPTY:
//                            return builder.isEmpty(expression);
                            criteria.append(" and ").append(fieldName).append(" == null ");
                            break;
                        case ISNOTEMPTY:
//                            return builder.isNotEmpty(expression);
                            criteria.append(" and ").append(fieldName).append(" != null ");
                            break;
                        case BETWEEN:
//                            return builder.between(expression, (Comparable)minValue , (Comparable) maxValue);
                            criteria.append(" and ").append(fieldName).append(" >= ").append(minValue);
                            criteria.append(" and ").append(fieldName).append(" <= ").append(maxValue);
                            break;
                        case IN:
                        case NOTIN:
//                            if ((value == null) || !(value instanceof Collection)) {
//                                return null;
//                            }
//                            Iterator iterator = ((Collection)value).iterator();
//                            CriteriaBuilder.In in = builder.in(expression);
//                            while (iterator.hasNext()) {
//                                in.value(iterator.next());
//                            }
//                            return (operator == Criterion.Operator.IN) ? in:builder.not(in);
                            valueList = se.getValueList();
                            if (valueList != null &&  valueList.size() >0){
                                StringBuilder sb = new StringBuilder();
                                Object tmpValue;
                                for (int i = 0; i < valueList.size(); i++){
                                    tmpValue = convertValue2Object(columnClass,valueList.get(i));
                                    if(i == 0){
                                        sb.append(" (").append("'").append(tmpValue).append("'");
                                    }else if(i == valueList.size() - 1){
                                        sb.append(",").append("'").append(tmpValue).append("'").append(")");
                                    }else{
                                        sb.append(",").append("'").append(tmpValue).append("'");
                                    }

                                }
                                criteria.append(" and ").append((se.getOperator() == Criterion.Operator.IN) ? " in ":" not in ").append(sb);
                            }
                            break;
                        default:
//                            return null;
                            break;
                    }
//                    criteria.add(se);
                }
            }
        }
        return criteria;
    }

    private StringBuilder processCriteriaInCondition(String[] arr){
        StringBuilder sb = new StringBuilder();
        for (int i = 0, size=arr.length; i < size; i++) {
            if (!StringUtils.isEmpty(arr[i])) {
                Object tmpValue = arr[i];
                if(i == 0){
                    sb.append(" (").append("'").append(tmpValue).append("'");
                }else if(i == size - 1){
                    sb.append(",").append("'").append(tmpValue).append("'").append(")");
                }else{
                    sb.append(",").append("'").append(tmpValue).append("'");
                }
            }
        }
        return sb;
    }
    protected Object convertValue2Object(Class clazz,Object value){
        if (value == null || clazz == null){
            return null;
        }
        Object newValue = value;
//        try {
//            if(Number.class.isAssignableFrom(clazz) && !(value instanceof Number)
//                    || Integer.class.isAssignableFrom(clazz) && !(value instanceof Integer)
//                    || Double.class.isAssignableFrom(clazz) && !(value instanceof Double)
//                    || Float.class.isAssignableFrom(clazz) && !(value instanceof Float)){
//                Method method = clazz.getMethod("valueOf", String.class);
//                newValue = method.invoke(null, value.toString());
//            }else if(Date.class.isAssignableFrom(clazz ) && !(value instanceof Date)){
//                newValue = DateUtil.parseDate(value);
//            }
//        } catch (Exception e) {
//            LogOperator.err(loggerName, "convertValue2Object error", e);
//        }
        return newValue;
    }
}